<?php


class KroExcel_PHPExcel extends KroExcel
{

	public $excel = null;

	public function createBook($file_suff = '')
	{
		parent::createBook($file_suff);
		require_once APPPATH.'libraries/PHPExcel.php';
		$this->excel = new PHPExcel();
		$this->excel->getProperties()->setCreator("MMMANK")
									 ->setLastModifiedBy("MMMANK")
									 ->setTitle("Balance")
		;
	}

	public function createSheetBalance($sheet_name, $sheet_num, $users)
	{
		global $VALUTA;

		$styleMediumBlackBorderOutline = array(
			'borders' => array(
				'outline' => array(
					'style' => PHPExcel_Style_Border::BORDER_MEDIUM,
					'color' => array('argb' => PHPExcel_Style_Color::COLOR_BLACK),
				),
			),
		);

		$styleYellowFill = array(
			'fill' => array(
	 			'type'       => PHPExcel_Style_Fill::FILL_SOLID,
	 			'color' => array(
	 				'argb' => PHPExcel_Style_Color::COLOR_YELLOW
	 			)
	 		)
		);

		$styleYellowFill_Center = $styleYellowFill;
		$styleYellowFill_Center['alignment'] = array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

		if($sheet_num) 
		{
			$this->excel->createSheet();
		}
		$this->excel->setActiveSheetIndex($sheet_num);
		$this->excel->getActiveSheet()->setTitle($sheet_name);

		$this->excel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
		$this->excel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
		$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(52);
		$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
		$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
		$this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(27);
		$this->excel->getActiveSheet()->getColumnDimension('H')->setWidth(75);
		$this->excel->getActiveSheet()->getColumnDimension('I')->setWidth(60);
		
		$this->excel->getActiveSheet()->getStyle('D:G')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//		$this->excel->getActiveSheet()->getStyle('A:I')->getFont()->setName('Times New Roman');

		$this->excel->getActiveSheet()
					->setCellValue('A1', 'Статус участника')
					->setCellValue('B1', 'Ф.И.О., (регион проживания), г.р.')
					->setCellValue('C1', 'Контактные данные: email, skype логин и моб.телефон')
					->setCellValue('D1', 'Дата')
					->setCellValue('E1', 'cумма покупки/продажи')
					->setCellValue('F1', 'Курс $МмМ покупки/продажи')
					->setCellValue('G1', '$МмМ')
					->setCellValue('H1', 'Вид и место хранения средств')
					->setCellValue('I1', 'Дополнительно')
		;

//		$this->excel->getActiveSheet()->getStyle('A1:I1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$this->excel->getActiveSheet()->getStyle('A1:I1')->applyFromArray($styleMediumBlackBorderOutline);

		$row_num = 2;
		$user_num = 1;
		$last_row_num = $row_num;

		foreach($users as $k => $user)
		{
			if(!@$user['operations'] || !count($user['operations']))
			{
				continue;
			}
			
			// Первая колонка

			$text_cell_0 = $user['id'] == Auth::$user_id ? '' : "№ ".$user_num;
			$this->excel->getActiveSheet()->setCellValue('A'.$row_num, $text_cell_0);
			$row_num_1 = $row_num + 1;
			$this->excel->getActiveSheet()->setCellValue('A'.($row_num + 1), $user['status']);
			if($user['referer_email'])
			{
				$row_num_1 += 3;
				$this->excel->getActiveSheet()->setCellValue('A'.($row_num + 2), 'Реферер:');
				$this->excel->getActiveSheet()->setCellValue('A'.($row_num + 3), $user['referer_email']);
				$this->excel->getActiveSheet()->setCellValue('A'.($row_num + 4), $user['referer_fio']);
			}

			// Вторая колонка
			$row_num_2 = $row_num + 2;
			$this->excel->getActiveSheet()->setCellValue('B'.($row_num + 1), $user['fio']);
			$this->excel->getActiveSheet()->setCellValue('B'.($row_num + 2), $user['otkuda']);

			// Третья колонка
			$row_num_3 = $row_num;
			if($user['email'])
			{
				$row_num_3++;
				$this->excel->getActiveSheet()->setCellValue('C'.$row_num_3, $user['email']);
			}
			if($user['phone'])
			{
				$row_num_3++;
				$this->excel->getActiveSheet()->setCellValue('C'.$row_num_3, "Телефон: ".$user['phone']);
			}
			if($user['skype'])
			{
				$row_num_3++;
				$this->excel->getActiveSheet()->setCellValue('C'.$row_num_3, "Skype: ".$user['skype']);
			}
			if($user['icq'])
			{
				$row_num_3++;
				$this->excel->getActiveSheet()->setCellValue('C'.$row_num_3, "ICQ: ".$user['icq']);
			}
			
			// Операции
			$row_num_oper = $row_num;
			foreach($user['operations'] as $oper)
			{
				$is_bonus = $oper['bonus'] && $oper['type'] != 'modify.buy' && $oper['type'] != 'modify.sell';
				$this->excel->getActiveSheet()->setCellValue('D'.$row_num_oper, $oper['day'].'.'.$oper['month'].'.'.$oper['year']);
				if($is_bonus)
				{
					$this->excel->getActiveSheet()->getStyle('D'.$row_num_oper)->applyFromArray($styleYellowFill_Center);
					$this->excel->getActiveSheet()->getStyle('E'.$row_num_oper)->applyFromArray($styleYellowFill_Center);
					$this->excel->getActiveSheet()->getStyle('F'.$row_num_oper)->applyFromArray($styleYellowFill_Center);
					$this->excel->getActiveSheet()->getStyle('G'.$row_num_oper)->applyFromArray($styleYellowFill_Center);
					$this->excel->getActiveSheet()->getStyle('H'.$row_num_oper)->applyFromArray($styleYellowFill);
					$this->excel->getActiveSheet()->getStyle('H'.$row_num_oper)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
				}

				$f_summ = isBuy($oper) ? 'buy' : 'sell';
				$this->excel->getActiveSheet()->setCellValue('E'.$row_num_oper, $oper['summ'].' '.$VALUTA[$oper['valuta']]);
				$summ_color = isBuy($oper) ? PHPExcel_Style_Color::COLOR_DARKGREEN : PHPExcel_Style_Color::COLOR_RED;
				$this->excel->getActiveSheet()->getStyle('E'.$row_num_oper)->getFont()->getColor()->setARGB($summ_color);

				$txt_kurs = $oper['type'] != 'week' && $oper['mavro'] > 0 ? $oper['valuta'].' '.$oper['kurs'].' '.$VALUTA[$oper['mavro_valuta']].' ('.$oper['percent'].'%)' : $oper['percent'].'%';
				$this->excel->getActiveSheet()->setCellValue('F'.$row_num_oper, $txt_kurs);
				$kurs_color = isBuy($oper) ? PHPExcel_Style_Color::COLOR_DARKBLUE2 : PHPExcel_Style_Color::COLOR_BLUE;
				$this->excel->getActiveSheet()->getStyle('F'.$row_num_oper)->getFont()->getColor()->setARGB($kurs_color);

				$f_mavro = isBuy($oper) ? 'buy' : 'sell';
				$f_mavro .= $is_bonus  ? "_yellow" : '';
				$txt_mavro = '';
				if($oper['type'] != 'week' && $oper['mavro'] > 0)
				{
					$txt_mavro = $oper['mavro'].' '.$oper['mavro_valuta'];
					if($oper['depo'])
					{
						$super = $oper['depo'] && substr($oper['percent'], -1) == 5 ? "super" : '';
						$txt_mavro .= '(Dep '.$oper['depo'].'m '.$super.')';
					}
				}
				$this->excel->getActiveSheet()->setCellValue('G'.$row_num_oper, $txt_mavro);
				$this->excel->getActiveSheet()->getStyle('G'.$row_num_oper)->getFont()->getColor()->setARGB($summ_color);

				$f_hranen = $is_bonus ? 'hranen_yellow' : 'cell';
				$this->excel->getActiveSheet()->setCellValue('H'.$row_num_oper, $oper['hranen']);

				$this->excel->getActiveSheet()->setCellValue('I'.$row_num_oper, $oper['dop']);

				$row_num_oper++;
			}
			$row_num_oper--;

			$row_num = max($row_num_1, $row_num_2, $row_num_3, $row_num_oper);


			// Добавление последней строки
			$row_num++;
			$all_balance = 'Общий баланс $МмМ:';
			if(@$user['all_mavro'])
			{
				foreach($user['all_mavro'] as $valuta => $arr_valuta)
				{
					foreach($arr_valuta as $percent => $m)
					{
						$all_balance .= ' '.$m.' '.$valuta.' ('.$percent.'%);';
					}
				}
			}
			$this->excel->getActiveSheet()->setCellValue('D'.$row_num, $all_balance);
			$this->excel->getActiveSheet()->mergeCells('D'.$row_num.':G'.$row_num);
			$row_num++;
			if($user['id'] != Auth::$user_id)
			{
				$user_num++;
			}
			$this->excel->getActiveSheet()->getStyle('A'.$last_row_num.':A'.($row_num - 1))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('B'.$last_row_num.':B'.($row_num - 1))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('C'.$last_row_num.':C'.($row_num - 1))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('D'.$last_row_num.':D'.($row_num - 2))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('E'.$last_row_num.':E'.($row_num - 2))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('F'.$last_row_num.':F'.($row_num - 2))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('G'.$last_row_num.':G'.($row_num - 2))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('H'.$last_row_num.':H'.($row_num - 1))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('I'.$last_row_num.':I'.($row_num - 1))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('D'.($row_num - 1).':G'.($row_num - 1))->applyFromArray($styleMediumBlackBorderOutline);
			$last_row_num = $row_num;
		}
	}

	public function closeBook()
	{
		$this->excel->setActiveSheetIndex(0);
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, $this->_getWriter());
		$objWriter->save($this->filename);
	}

	protected function _getWriter()
	{
		return null;
	}

}



